﻿using MySql.Data.MySqlClient;

using RantopadService.Conn;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;

namespace RantopadService.Business
{
    public class ClientBusiness
    {

        public static DataTable getAllClients(string wherelike, string typeID)
        {
            string mysql = "SELECT a.*,b.NAME as TYPE_NAME from client  a,client_type  b  where a.NAME like '%{0}%'  and a.TYPE_ID=b.ID  and b.ID like '%{1}%'";
            mysql = string.Format(mysql, wherelike, typeID);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);
            myconnection.Close();

            return dt;
        }
        public static DataTable getNewClient(string typeID)
        {
            string mysql = "SELECT a.*,b.NAME as TYPE_NAME from client  a,client_type  b  where a.TYPE_ID=b.ID  and b.id='{0}' and a.v=(select max(v) from client where TYPE_ID='{0}' )";
            mysql = string.Format(mysql,  typeID);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);
            myconnection.Close();

            return dt;
        }
        public static DataTable getAllClientsType(string wherelike)
        {
            string mysql = "SELECT * from client_type  where NAME like '%{0}%' ";
            mysql = string.Format(mysql, wherelike);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            var dataAdapter = new MySqlDataAdapter(mysql, myconnection);
            var dt = new DataTable();
            dataAdapter.Fill(dt);
            myconnection.Close();

            return dt;
        }

        public static int getAllClientsCount(string wherelike)
        {
            string mysql = "SELECT count(ID) as count from client where NAME like '%{0}%' ";
            mysql = string.Format(mysql, wherelike);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
            MySqlDataReader dr = mycommand.ExecuteReader();

            if (!dr.Read())
            {
                myconnection.Close();
return 0;
            }
                
            var countstr = dr["count"].ToString();
            var count = Convert.ToInt32(countstr);
            myconnection.Close();
            return count;
        }
        public static int getAllClientsTypeCount(string wherelike)
        {
            string mysql = "SELECT count(ID) as count from client_type where NAME like '%{0}%' ";
            mysql = string.Format(mysql, wherelike);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
            MySqlDataReader dr = mycommand.ExecuteReader();

            if (!dr.Read())
            {
                myconnection.Close();
                return 0; }
            var countstr = dr["count"].ToString();
            var count = Convert.ToInt32(countstr);
            myconnection.Close();
            return count;
        }
        public static int checkData(string colNM, string clientID)
        {
            string mysql = "SELECT count(ID) as count from client where {0} = '{1}' ";
            mysql = string.Format(mysql, colNM, clientID);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
            MySqlDataReader dr = mycommand.ExecuteReader();

            if (!dr.Read())
            { myconnection.Close(); return 0; }
            var countstr = dr["count"].ToString();
            var count = Convert.ToInt32(countstr);
            myconnection.Close();
            return count;
        }

        public static int checkClientName(string clientName)
        {
            var count = checkData("NAME", clientName);
            return count;
        }
        public static int delClientByID(string id)
        {
            string mysql = "delete from client where ID='{0}'";
            mysql = string.Format(mysql, id);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

            var count = mycommand.ExecuteNonQuery();

            myconnection.Close();
            return count;
        }
        public static int delClientTypeByID(string id)
        {
            string mysql = "delete from client_type where ID='{0}'";
            mysql = string.Format(mysql, id);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

            var count = mycommand.ExecuteNonQuery();

            myconnection.Close();
            return count;
        }


        public static int addClient(string name, string URI, string typeID, string v)
        {
            //INSERT INTO `rantopad`.`client` (`NAME`, `USER_PSW`, `EMAIL`, `ONLINE`) VALUES ('aaaaa', 'a', 'liang@163.com', '0');
            int count = 0;
            try
            {
                string mysql = "insert into `client` (`NAME`, `URI`, `TYPE_ID`, `V`) VALUES ('{0}', '{1}', '{2}','{3}') ";
                mysql = string.Format(mysql, name, URI, typeID, v);
                //创建数据库连接
                var myconnection = MyMySqlHelper.GetConn();
                myconnection.Open();
                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
            }
            catch (Exception)
            {

                count = 0;
            }


            return count;
        }
        public static int addClientType(string name)
        {

            int count = 0;
            try
            {
                string mysql = "insert into `client_type` (`NAME`) VALUES ('{0}') ";
                mysql = string.Format(mysql, name);
                //创建数据库连接
                var myconnection = MyMySqlHelper.GetConn();
                myconnection.Open();
                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
            }
            catch (Exception)
            {

                count = 0;
            }


            return count;
        }

        internal static int updateClient(string typeID, string name, string URI, string id, string v)
        {
            int count = 0;
            try
            {
                string mysql = "update `client` set `NAME`='{0}', `URI`='{1}', `TYPE_ID`='{2}', `V`='{3}'  where `ID`='{4}'";
                mysql = string.Format(mysql, name, URI, typeID, v, id);
                //创建数据库连接
                var myconnection = MyMySqlHelper.GetConn();
                myconnection.Open();
                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
            }
            catch (Exception)
            {

                count = 0;
            }


            return count;
        }
        internal static int updateClientType(string typeName, string id)
        {
            int count = 0;
            try
            {
                string mysql = "update `client_type` set `NAME`='{1}'  where `ID`='{0}'";
                mysql = string.Format(mysql, id, typeName);
                //创建数据库连接
                var myconnection = MyMySqlHelper.GetConn();
                myconnection.Open();
                MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);

                count = mycommand.ExecuteNonQuery();
                myconnection.Close();
            }
            catch (Exception)
            {
                count = 0;
            }
            return count;
        }
        public static string getClientsTypeID(string name)
        {
            string mysql = "SELECT * from client_type   where NAME like '%{0}%' ";
            mysql = string.Format(mysql, name);
            //创建数据库连接
            var myconnection = MyMySqlHelper.GetConn();
            myconnection.Open();
            //创建MySqlCommand对象
            MySqlCommand mycommand = new MySqlCommand(mysql, myconnection);
            MySqlDataReader dr = mycommand.ExecuteReader();

            if (!dr.Read())
            { myconnection.Close(); return ""; }
            var id = dr["ID"].ToString();
            myconnection.Close();
            return id;
        }
    }
}